前言
本文主要介绍 Flask SQLAlchemy 的具体使用。
如需要了解 Flask 入门文档可跳转：
Setup
Install MySQL/MariaDB
1. Installation
MariaDB is the default implementation of MySQL in Arch Linux, provided with the mariadb package.
Install mariadb, and run the following command before starting the mariadb.service
# mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
2. Configuration
Once you have started the MariaDB server and added a root account, you may want to change the default configuration.
To log in as root on the MariaDB server, use the following command:
# mariadb -u root -p
2.1 Add user
Creating a new user takes two steps: create the user; grant privileges. In the below example, the user monty with some_pass as password is being created, then granted full permissions to the database mydb:
# mariadb -u root -p
MariaDB> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'monty'@'localhost';
MariaDB> quit
安装 PyMySQL 和 Flask-SQLAlchemy
使用python 提供 pip 包管理器安装 pymysql 和 Flask-SQLAlchemy
$ pip install PyMySQL
$ pip install flask-sqlalchemy
Connection URI Format
For a complete list of connection URIs head over to the SQLAlchemy documentation under (Supported Databases). This here shows some common connection strings.
SQLAlchemy indicates the source of an Engine as a URI combined with optional keyword arguments to specify options for the Engine. The form of the URI is:
dialect+driver://username:password@host:port/database
MySQL:
mysql://scott:tiger@localhost/mydatabase
Configuration Keys
SQLALCHEMY_DATABASE_URI
The database URI that should be used for the connection. Examples:
- sqlite:////tmp/test.db
- mysql://username:password@server/db
创建数据库
安装完上边的套件后，就可以正式创建Mysql数据库了。
使用root用户创建数据库
sudo mariadb -u root -p
进入mariadb后，使用如下命令创建数据库：
MariaDB [(none)]> create database proj_20230325;
Query OK, 1 row affected (0.000 sec)
给予权限
之后给予用户 Yarnom 该数据库的所有权限：
MariaDB [(none)]> GRANT ALL PRIVILEGES ON proj_20230325.* TO 'yarnom'@'localhost';
Query OK, 0 rows affected (0.009 sec)
安装 FLask-Migrate
$ pip install Flask-Migrate
配置
from flask_migrate import Migrate
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://yarnom:root@localhost:3306/proj_20230325"
db = SQLAlchemy(app)
Migrate(app,db)
初始化
$ flask db init
Flask 使用Mysql数据库
创建出相应的模板：
class Student(db.Model):
id = db.Column('id', db.String(100),primary_key=True)
name = db.Column('name',db.String(100))
def __init__(self, id, name):
self.id =id
self.name = name
使用如下命令更新数据库
$ flask db migrate -m "說明文字"
$ flask db upgrade
Flask-Sqlalchemy 使用
以下内容转载自flask-sqlalchemy 官方文档
Flask 添加数据
me = User('admin', '[email protected]')
db.session.add(me)
db.session.commit()
Flask 查询数据
So how do we get data back out of our database? For this purpose Flask-SQLAlchemy provides a query attribute on your Model class. When you access it you will get back a new query object over all records. You can then use methods like filter() to filter the records before you fire the select with all() or first(). If you want to go by primary key you can also use get().
The following queries assume following entries in the database: |id|username|email| |---|---|---| |1|admin|[email protected]| |2|peter|[email protected]| |3|guest|[email protected]|
Retrieve a user by username:
>>> peter = User.query.filter_by(username='peter').first()
>>> peter.id
2
>>> peter.email
u'[email protected]'
Same as above but for a non existing username gives None:
>>> missing = User.query.filter_by(username='missing').first()
>>> missing is None
True
Selecting a bunch of users by a more complex expression:
>>> User.query.filter(User.email.endswith('@example.com')).all()
[<User u'admin'>, <User u'guest'>]
Ordering users by something:
>>> User.query.order_by(User.username).all()
[<User u'admin'>, <User u'guest'>, <User u'peter'>]
Limiting users:
>>> User.query.limit(1).all()
[<User u'admin'>]
Getting user by primary key:
>>> User.query.get(1)
<User u'admin'>